system = """You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 5 results.
You can order the results by a relevant column to return the most interesting examples in the database.
Never query for all the columns from a specific table, only ask for the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the given tools. Only use the information returned by the tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.
Try to use the left join notation as much as possible.
You have access to the following tables: {table_names}.
The relationship between tables:{description},
除此之外，表跟表直接没有其他关联关系
"""


choice_table_prompt = """You are an agent designed to interact with a SQL database.
Given an input question, Return a main table related to the question.
You have access to the following tables: {table_names}.
"""


extrac_prompt = r'关联表:(\w+)表'



table_schema = """
CREATE TABLE sludge_transport_record (
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
	numberplate VARCHAR(10) NOT NULL COMMENT '车牌号', 
	weight_in DOUBLE COMMENT '进厂称重', 
	weight_out DOUBLE COMMENT '出厂称重', 
	time_in TIMESTAMP NULL COMMENT '进厂时间', 
	time_out TIMESTAMP NULL COMMENT '出厂时间', 
	sludge_weight DOUBLE COMMENT '污泥重量', 
	water_content DOUBLE COMMENT '污泥含水率', 
	create_uid INTEGER UNSIGNED NOT NULL, 
	update_uid INTEGER UNSIGNED, 
	created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	deleted_at INTEGER UNSIGNED DEFAULT '0', 
	department_id INTEGER UNSIGNED NOT NULL COMMENT '部门id,关联表为department表' DEFAULT '0', 
	shift VARCHAR(50) NOT NULL COMMENT '班次' DEFAULT '', 
	destination VARCHAR(1000) NOT NULL COMMENT '出库去向' DEFAULT '', 
	signature VARCHAR(100) NOT NULL COMMENT '出库人签字' DEFAULT '', 
	PRIMARY KEY (id)
)ENGINE=InnoDB COMMENT='污泥外运记录表' DEFAULT CHARSET=utf8mb3

/*
3 rows from sludge_transport_record table:
id	numberplate	weight_in	weight_out	time_in	time_out	sludge_weight	water_content	create_uid	update_uid	created_at	updated_at	deleted_at	department_id	shift	destination	signature
5	浙F93501	2000.0000000000	1500.0000000000	2024-05-22 00:00:00	2024-05-30 00:00:00	500.0000000000	0E-10	147	None	2024-05-30 14:01:01	2024-11-05 10:04:01	0	59	白班	嘉爱斯	张总
6	浙F7772Y	1100.0000000000	500.0000000000	2024-06-11 14:00:00	2024-06-11 18:00:00	600.0000000000	0E-10	147	None	2024-06-12 14:37:02	2024-11-05 10:04:01	0	59	夜班	嘉艾斯	张总
7	浙FQ135T	2222.0000000000	1111.0000000000	2024-06-18 10:00:00	2024-06-19 00:00:00	1111.0000000000	0E-10	147	None	2024-06-18 13:13:45	2024-11-05 10:04:01	0	59	白班	嘉艾斯	张总
*/

"""


schema = """
CREATE TABLE ding_talk_attendance (
	id BIGINT UNSIGNED NOT NULL, 
	source_type VARCHAR(255) COMMENT '来源', 
	base_check_time BIGINT COMMENT '基准签到时间' DEFAULT '0', 
	user_check_time BIGINT COMMENT '实际打卡时间, 用户打卡时间的毫秒数' DEFAULT '0', 
	proc_inst_id VARCHAR(255) COMMENT '关联的审批实例ID，当该字段非空时，表示打卡记录与请假、加班等审批有关' DEFAULT '', 
	approve_id VARCHAR(255) COMMENT '关联的审批ID，当该字段非空时，表示打卡记录与请假、加班等审批有关' DEFAULT '', 
	location_result VARCHAR(255) COMMENT '位置结果' DEFAULT '', 
	time_result VARCHAR(255) COMMENT '打卡结果' DEFAULT '', 
	check_type VARCHAR(255) COMMENT '考勤类型' DEFAULT '', 
	dingtalk_user_id VARCHAR(200) COMMENT '考勤人员id,关联表:dingtalk_user表', 
	user_id INTEGER UNSIGNED NOT NULL COMMENT '用户id', 
	work_date BIGINT COMMENT '工作日' DEFAULT '0', 
	record_id BIGINT COMMENT '打卡记录ID' DEFAULT '0', 
	plan_id BIGINT COMMENT '排班ID' DEFAULT '0', 
	group_id INTEGER COMMENT '考勤组ID' DEFAULT '0', 
	create_uid INTEGER UNSIGNED DEFAULT '0', 
	update_uid INTEGER UNSIGNED, 
	created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	deleted_at INTEGER UNSIGNED DEFAULT '0', 
	PRIMARY KEY (id)
)DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_0900_ai_ci ENGINE=InnoDB COMMENT='钉钉考勤结果记录表'

/*
3 rows from ding_talk_attendance table:
id	source_type	base_check_time	user_check_time	proc_inst_id	approve_id	location_result	time_result	check_type	dingtalk_user_id	user_id	work_date	record_id	plan_id	group_id	create_uid	update_uid	created_at	updated_at	deleted_at
349457782461	USER	1729490400000	1729508049000			Normal	Late	OnDuty	020628151124053635	0	1729440000000	171238490332	729682053608	1197110225	0	None	2024-10-22 03:01:23	2024-10-22 04:00:55	0
349561124059	USER	1729579320000	1729579360000			Normal	Normal	OnDuty	0109652742411030824	0	1729526400000	170092904762	730138133068	-1	0	None	2024-10-22 22:43:24	2024-10-22 22:44:30	0
349707583259	USER	1729668480000	1729668518000			Normal	Normal	OnDuty	0109652742411030824	0	1729612800000	171439352994	732866759798	-1	0	None	2024-10-24 00:20:13	2024-10-24 00:20:13	0
*/
"""

second_schema = """
CREATE TABLE dingtalk_user (
	user_id INTEGER UNSIGNED NOT NULL COMMENT='用户id,关联表为user表', 
	dingtalk_user_id VARCHAR(32) NOT NULL, 
	staff_id INTEGER UNSIGNED NOT NULL, 
	PRIMARY KEY (user_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='钉钉用户表'

/*
3 rows from dingtalk_user table:
user_id	dingtalk_user_id	staff_id
2	204160460821684226	1
3	1349530746385748555	2
4	2456374834755036	3
*/
"""